﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_personal_annual')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_personal_annual

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-07-21 00:24:02
-- Database:    [scm_main-hysj2]
-- Description: 个人中心-员工年假信息
-- =============================================

CREATE PROCEDURE bd_proc_personal_annual
  @userSerial BIGINT
AS
BEGIN
  SET NOCOUNT ON;


  CREATE TABLE #result
  (
    user_serial BIGINT,
    user_no VARCHAR(50),
    user_name VARCHAR(100),
    dep_id BIGINT,
    dep_name VARCHAR(100),
    annual_total INT DEFAULT 0,
    annual_usage INT DEFAULT 0,
    annual_remain INT DEFAULT 0,

    annual_total_hours DECIMAL(8, 2) DEFAULT 0,
    annual_usage_hours DECIMAL(8, 2) DEFAULT 0,
    annual_remain_hours DECIMAL(8, 2) DEFAULT 0,

    annual_add INT,
    annual_deduct INT,
    annual_cancel INT
  )

  INSERT INTO #result(user_serial, user_no, user_name, dep_id, dep_name)
  SELECT user_serial, user_no, user_lname, user_dep, dep_name
  FROM bd_view_user_all
  WHERE user_serial = @userSerial

  DECLARE @settleDate DATETIME
         ,@year INT

  SET @settleDate = dbo.bd_fn_getSettleDate(GETDATE())
  SELECT @year = YEAR(@settleDate)

  --1：年假申请
  --2：年假录入增加
  --3：年假录入减少
  --4：年假取消
  --5：每年年初初始化
  --6：年假清零
  --8：上线初始化

  --录入	剩余>扣除前	增加
  --	    剩余<扣除前	减少

  DECLARE @value INT

  --总年假
  SELECT @value = SUM(deduct_usage)
  FROM bd_kq_holidays
  WHERE user_serial = @userSerial AND ho_year = @year AND ho_type IN (2, 5, 8)

  UPDATE #result
  SET annual_add = ISNULL(@value, 0)

  UPDATE #result SET annual_total = annual_add

  --已扣除年假，包含已取消的
  SELECT @value = SUM(deduct_usage)
  FROM bd_kq_holidays
  WHERE user_serial = @userSerial AND ho_year = @year AND ho_type IN (1, 3, 6)

  UPDATE #result
  SET annual_deduct = ISNULL(@value, 0)

  --已取消的年假
  SELECT @value = SUM(deduct_usage)
  FROM bd_kq_holidays
  WHERE user_serial = @userSerial AND ho_year = @year AND ho_type = 4

  UPDATE #result
  SET annual_cancel = ISNULL(@value, 0)

  --已使用
  UPDATE #result
  SET annual_usage = ISNULL(annual_deduct, 0) - ISNULL(annual_cancel, 0)

  --当前剩余年假
  SELECT @value = vacation_annual
  FROM bd_view_kq_holidays_time_rowno
  WHERE user_serial = @userSerial AND row_no = 1

  UPDATE #result
  SET annual_remain = @value


  UPDATE #result
  SET annual_total_hours = CAST(annual_total / 60.0 AS DECIMAL(8, 2))
     ,annual_usage_hours = CAST(annual_usage / 60.0 AS DECIMAL(8, 2))
     ,annual_remain_hours = CAST(annual_remain / 60.0 AS DECIMAL(8, 2))


  SELECT user_serial, user_no, user_name, dep_id, dep_name, annual_total, annual_usage, annual_remain, annual_total_hours, annual_usage_hours, annual_remain_hours
  FROM #result

    SELECT * FROM #result r
 

  DROP TABLE #result

  SET NOCOUNT OFF;
END